package cs9322.assign3.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import com.mysql.jdbc.Statement;

import cs9322.assign3.model.Order;

public class OrderDao {
	Connection connection = null;
	PreparedStatement ptmt = null;
	ResultSet resultSet = null;

	public OrderDao() {
	}

	private Connection getConnection() throws SQLException {
		Connection conn;
		conn = ConnectionFactory.getInstance().getConnection();
		return conn;
	}

	public int add(Order order) {
		try {
			String queryString = "INSERT INTO coffee_order(type, size, paid, addition, status, price, url) VALUES(?,?,?,?,?,?,?)";
			connection = getConnection();
			ptmt = connection.prepareStatement(queryString,
					Statement.RETURN_GENERATED_KEYS);
			ptmt.setString(1, order.getType());
			ptmt.setString(2, order.getSize());
			ptmt.setBoolean(3, order.isPaid());
			ptmt.setString(4, order.getAddition());
			ptmt.setString(5, order.getStatus());
			ptmt.setDouble(6, order.getPrice());
			ptmt.setString(7, order.getUrl());
			ptmt.executeUpdate();
			int id =0;
			resultSet = ptmt.getGeneratedKeys();
			if(resultSet.next()){
				id = resultSet.getInt(1);
			}
			System.out.println("Order Added Successfully");
			return id;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (ptmt != null)
					ptmt.close();
				if (connection != null)
					connection.close();
			} catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();
			}

		}
		return 100;
	}

	public void update(Order order2) {

		try {
			String queryString = "UPDATE coffee_order SET type=?, size=?, paid=?, addition=?, status=?, price=?, url=? where idorder=?";
			connection = getConnection();
			ptmt = connection.prepareStatement(queryString);
			ptmt.setString(1, order2.getType());
			ptmt.setString(2, order2.getSize());
			ptmt.setBoolean(3, order2.isPaid());
			ptmt.setString(4, order2.getAddition());
			ptmt.setString(5, order2.getStatus());
			ptmt.setDouble(6, order2.getPrice());
			ptmt.setString(7, order2.getUrl());
			ptmt.setInt(8, order2.getId());
			ptmt.executeUpdate();
			System.out.println("Order Updated Successfully");
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (ptmt != null)
					ptmt.close();
				if (connection != null)
					connection.close();
			}

			catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();

			}
		}
	}
	
	 public void delete(int id3) {

         try {
                 String queryString = "DELETE FROM coffee_order WHERE idorder=?";
                 connection = getConnection();
                 ptmt = connection.prepareStatement(queryString);
                 ptmt.setInt(1, id3);
                 ptmt.executeUpdate();
                 System.out.println("Data deleted Successfully");
         } catch (SQLException e) {
                 e.printStackTrace();
         } finally {
                 try {
                         if (ptmt != null)
                                 ptmt.close();
                         if (connection != null)
                                 connection.close();
                 } catch (SQLException e) {
                         e.printStackTrace();
                 } catch (Exception e) {
                         e.printStackTrace();
                 }

         }

 }
	public ArrayList<Order> getAllOrders() {
		ArrayList<Order> result = new ArrayList<Order>();
		Order o = null;
		try {
			String queryString = "select * from coffee_order";
			connection = getConnection();
			ptmt = connection.prepareStatement(queryString);
			resultSet = ptmt.executeQuery();

			while (resultSet.next()) {
				o = new Order();
				o.setId(resultSet.getInt("idorder"));
				o.setType(resultSet.getString("type"));
				o.setSize(resultSet.getString("size"));
				o.setPaid(resultSet.getBoolean("paid"));
				o.setAddition(resultSet.getString("addition"));
				o.setPrice(resultSet.getDouble("price"));
				o.setStatus(resultSet.getString("status"));
				o.setUrl(resultSet.getString("url"));
				result.add(o);
			}
			return result;
		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (ptmt != null)
					ptmt.close();
				if (connection != null)
					connection.close();
			}

			catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();

			}
		}
		return result;
	}

	public Order findById(int id) {
		Order o = null;
		try {
			String queryString = "select * from coffee_order where idorder=?";
			connection = getConnection();
			ptmt = connection.prepareStatement(queryString);
			ptmt.setInt(1, id);
			resultSet = ptmt.executeQuery();

			while (resultSet.next()) {
				o = new Order();
				o.setId(resultSet.getInt("idorder"));
				o.setType(resultSet.getString("type"));
				o.setSize(resultSet.getString("size"));
				o.setPaid(resultSet.getBoolean("paid"));
				o.setAddition(resultSet.getString("addition"));
				o.setPrice(resultSet.getDouble("price"));
				o.setStatus(resultSet.getString("status"));
				o.setUrl(resultSet.getString("url"));
			}
			return o;

		} catch (SQLException e) {
			e.printStackTrace();
		} finally {
			try {
				if (ptmt != null)
					ptmt.close();
				if (connection != null)
					connection.close();
			}

			catch (SQLException e) {
				e.printStackTrace();
			} catch (Exception e) {
				e.printStackTrace();

			}
		}
		return o;
	}
}
